package com.gem.growingtree.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import com.gem.growingtree.db.DBLink;
import com.gem.growingtree.model.PageBean;
import com.gem.growingtree.model.ParentAccount;
import com.gem.growingtree.model.ShowParents;

//显示家长账户信息业务逻辑
public class ParentAccountService {
	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;

	public void close(){
		if(rs != null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(pstmt != null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(conn != null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

//显示所以家长信息
	public PageBean findByPage(int pageCode, int pageSize) throws Exception {
		if(pageCode<=0)
		{
			pageCode=1;
		}
		PageBean pb = new PageBean();
		ParentAccount parentAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(userId) from users where identity = 1";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);

		sql = "SELECT userId,userName, className, parentName, babyName FROM users,classes ,parent ,baby WHERE users.userId = parent.parentId AND parent.babyId = baby.babyId AND baby.classId = classes.classId  ORDER BY parentId limit ?,?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setInt(1, (pageCode-1)*pageSize);
		pstmt.setInt(2, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String userId = rs.getString(1);
			String userName = rs.getString(2);
			String className = rs.getString(3);
			String parentName = rs.getString(4);
			String babyName = rs.getString(5);
			parentAccount = new ParentAccount(userId,userName,className,parentName,babyName);
			pb.getList().add(parentAccount);
		}
		return pb;
	}
//显示具体的家长信息
	public PageBean findByPage2(int pageCode, int pageSize,String userId) throws Exception {
		PageBean pb = new PageBean();
		ParentAccount parentAccount = null;
		conn = DBLink.getConnection();
		String sql = "select count(userId) from users where identity = 1";
		pstmt = conn.prepareStatement(sql);
		rs = pstmt.executeQuery();
		if(rs.next()){
			pb.setAllCount(rs.getInt(1));
		}
		pb.setPageSize(pageSize);
		pb.setPageCode(pageCode);
		sql = "SELECT userId,userName, className, parentName, babyName FROM users,classes ,parent ,baby WHERE users.userId = parent.parentId AND parent.babyId = baby.babyId AND baby.classId = classes.classId and userId=? GROUP BY classes.className limit ?,?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, userId);
		pstmt.setInt(2, (pageCode-1)*pageSize);
		pstmt.setInt(3, pageSize);
		rs = pstmt.executeQuery();
		while(rs.next()){
			String parentId = rs.getString(1);
			String userName = rs.getString(2);
			String className = rs.getString(3);
			String parentName = rs.getString(4);
			String babyName = rs.getString(5);
			parentAccount = new ParentAccount(parentId,userName,className,parentName,babyName);
			pb.getList().add(parentAccount);
		}
		return pb;
	}
	//更新家长信息
	public boolean updateParents(int userId, ShowParents showParents)
			throws SQLException {
		boolean flag = false;
		conn = DBLink.getConnection();
		String sql = "update parent set parentNickName=?,parentName=?,parentPhoneNumber=?,address=? where parentId=?";
		pstmt = conn.prepareStatement(sql);
		pstmt.setString(1, showParents.getParentNickName());
		pstmt.setString(2, showParents.getParentName());
		pstmt.setString(3, showParents.getParentPhoneNumber());
		pstmt.setString(4, showParents.getAddress());
		pstmt.setString(5, showParents.getUserId());
		int rows = pstmt.executeUpdate(sql);
		if (rows == 1) {
			flag = true;
		}
		return flag;
	}
	//删除家长信息
	public boolean deleteParentInfo(String parentId ) {
		boolean flag = false;
		conn = DBLink.getConnection();
		String sql = "delete from parent where parentId in(" + parentId + ")";
		try {
			pstmt = conn.prepareStatement(sql);
			int rows = pstmt.executeUpdate(sql);
			if (rows > 0)
			{
				flag = true;
				System.out.println(flag);
				System.out.println("家长信息删除成功");
			} else {
				System.out.println("删除失败");
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return flag;
	}	
}
